package Laborum2.repository;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Component;

import Laborum2.Basicas.*;

@Component
public class JdbcPostulanteRepository implements PostulanteRepository {

	@Autowired
	private DataSource dataSource;
	
	List<Postulantes> pos = new ArrayList<Postulantes>();
	

	Postulantes post = new Postulantes();
	
	
	public List<Postulantes> obtenerPostulantes() {
       List<Postulantes> pos = new ArrayList<Postulantes>();
       Postulantes post = new Postulantes();
	
		String sql = "SELECT * from postulantes where estado=1";

		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = dataSource.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				post = new Postulantes();
				post.setDni(rs.getInt("dni"));
				post.setNombre(rs.getString("nombre"));
				post.setPaterno(rs.getString("paterno"));
				post.setMaterno(rs.getString("materno"));
				post.setFecnac(rs.getDate("fecnac"));
				post.setNac(rs.getString("nac"));
				post.setTipdoc(rs.getString("tipdoc"));
				post.setGenero(rs.getString("genero"));
			    post.setEstadocivil(rs.getString("estadocivil"));
			    post.setTelefono(rs.getInt("telefono"));
			    post.setCelular(rs.getInt("celular"));
			    post.setCorreo(rs.getString("correo"));
			    post.setPais(rs.getInt("pais"));
			    post.setDepar(rs.getInt("depar"));
			    post.setProvincia(rs.getInt("provincia"));
			    post.setDistrito(rs.getInt("distrito"));
			    post.setEstado(rs.getInt("estado"));
				pos.add(post);
			}	
			
		} catch (SQLException ex) {

		}
		closeConecction(rs, ps, conn);
		return pos;
	}

	private void closeConecction(ResultSet rs, PreparedStatement ps,
			Connection conn) {

		if (rs != null) {
			try {
				// Close to prevent database cursor exhaustion
				rs.close();
			} catch (SQLException ex) {
			}
		}
		if (ps != null) {
			try {
				// Close to prevent database cursor exhaustion
				ps.close();
			} catch (SQLException ex) {
			}
		}
		if (conn != null) {
			try {
				// Close to prevent database connection exhaustion
				conn.close();
			} catch (SQLException ex) {
			}
		}

	}

	private void closeConecction1(PreparedStatement ps, Connection conn) {

		if (ps != null) {
			try {
				// Close to prevent database cursor exhaustion
				ps.close();
			} catch (SQLException ex) {
			}
		}
		if (conn != null) {
			try {
				// Close to prevent database connection exhaustion
				conn.close();
			} catch (SQLException ex) {
			}
		}

	}

	@Override
	public Postulantes obtenerPostulantesDNI(int dni) {
		
		String sql = "select * from postulantes where dni=? and estado=1";
		
		Postulantes post = new Postulantes();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = dataSource.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, dni);
			rs = ps.executeQuery();

			while (rs.next()) {
				post = new Postulantes();
				post.setDni(rs.getInt("dni"));
				post.setNombre(rs.getString("nombre"));
				post.setPaterno(rs.getString("paterno"));
				post.setMaterno(rs.getString("materno"));
				post.setFecnac(rs.getDate("fecnac"));
				post.setNac(rs.getString("nac"));
				post.setTipdoc(rs.getString("tipdoc"));
				post.setGenero(rs.getString("genero"));
			    post.setEstadocivil(rs.getString("estadocivil"));
			    post.setTelefono(rs.getInt("telefono"));
			    post.setCelular(rs.getInt("celular"));
			    post.setCorreo(rs.getString("correo"));
			    post.setPais(rs.getInt("pais"));
			    post.setDepar(rs.getInt("depar"));
			    post.setProvincia(rs.getInt("provincia"));
			    post.setDistrito(rs.getInt("distrito"));
			    post.setEstado(rs.getInt("estado"));
		
			}

		} catch (SQLException ex) {

		}
		closeConecction(rs, ps, conn);
		return post;
	}

	public void editar(int dni, Postulantes postulantes) {

		if (!obtenerPostulantesDNI(dni).equals(dni)) {

			String sql = "update postulantes set dni=?, nombre=?, paterno=?, materno=?, fecnac=?, nac=?,tipdoc=?, genero=?, estadocivil=?, dire=?, telefono=?,celular=?,correo=? ,pais=?, depar=?, provincia=?, distrito=? where  dni=?" ;

			
			Connection conn = null;
			PreparedStatement ps = null;

			try {
				conn = dataSource.getConnection();
				ps = conn.prepareStatement(sql);
				ps.setInt(1, postulantes.getDni());
				ps.setString(2, postulantes.getNombre());
				ps.setString(3, postulantes.getPaterno());
				ps.setString(4, postulantes.getMaterno());
			//	ps.setDate(5, (Date) postulantes.getFecnac());
				ps.setString(6, postulantes.getNac());
				ps.setString(7, postulantes.getTipdoc());
				ps.setString(8, postulantes.getGenero());
				ps.setString(9, postulantes.getEstadocivil());
				ps.setString(10, postulantes.getDire());
				ps.setInt(11, postulantes.getTelefono());
				ps.setInt(12, postulantes.getCelular());
				ps.setString(13, postulantes.getCorreo());
				ps.setInt(14, postulantes.getPais());
				ps.setInt(15, postulantes.getDepar());
				ps.setInt(16, postulantes.getProvincia());
				ps.setInt(17, postulantes.getDistrito());
				ps.setInt(18, postulantes.getDni());

				ps.execute();
			} catch (SQLException ex) {
			}
			closeConecction1(ps, conn);
		} else {
			//throw new RuntimeException("Usuario ya registrado");
		}

	}

	@Override
	public void eliminar(int dni) {
		
		String sql = "update postulantes set  estado= 0 where  dni=?" ;

		
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = dataSource.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, dni);
		

			ps.execute();
		} catch (SQLException ex) {
		}
		closeConecction1(ps, conn);
	

	}

	@Override
	public void ingresar(Postulantes postulantes) {

		if (!obtenerPostulantesDNI(postulantes.getDni()).equals(postulantes.getDni())) {
		
			String sql = "insert into postulantes (dni, nombre, paterno, materno, fecnac, nac,tipdoc, genero, estadocivil, dire, telefono,celular,correo ,pais, depar, provincia ,distrito) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	
			Connection conn = null;
			PreparedStatement ps = null;

			try {
				conn = dataSource.getConnection();
				ps = conn.prepareStatement(sql);
				ps.setInt(1, postulantes.getDni());
				ps.setString(2, postulantes.getNombre());
				ps.setString(3, postulantes.getPaterno());
				ps.setString(4, postulantes.getMaterno());
				//ps.setDate(5, (Date) postulantes.getFecnac());
				ps.setString(6, postulantes.getNac());
				ps.setString(7, postulantes.getTipdoc());
				ps.setString(8, postulantes.getGenero());
				ps.setString(9, postulantes.getEstadocivil());
				ps.setString(10, postulantes.getDire());
				ps.setInt(11, postulantes.getTelefono());
				ps.setInt(12, postulantes.getCelular());
				ps.setString(13, postulantes.getCorreo());
				ps.setInt(14, postulantes.getPais());
				ps.setInt(15, postulantes.getDepar());
				ps.setInt(16, postulantes.getProvincia());
				ps.setInt(17, postulantes.getDistrito());
				ps.setInt(18, postulantes.getEstado());
				ps.execute();
			} catch (SQLException ex) {
			}
			closeConecction1(ps, conn);
		} else {
			throw new RuntimeException("Usuario ya registrado");
		}

	}

	@Override
	public List<Postulantes> obtenerPostulantesPorEspecialidad(String descripcion) {
		
		List<Postulantes> pos = new ArrayList<Postulantes>();
		String sql = "Select Lab.* From  Laboral Lab where Lab.Dni in (Select Detest.Dni  from detalleEstudios Detest Join estudios Est on Est.id = DetEst.estudios and desEst = ?)";
		Postulantes post = new Postulantes();

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = dataSource.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, descripcion);
			rs = ps.executeQuery();

			while (rs.next()) {
				post = new Postulantes();
				post.setDni(rs.getInt("dni"));
			    post.setCorreo(rs.getString("correo"));
				pos.add(post);
			}

		} catch (SQLException ex) {

		}
		closeConecction(rs, ps, conn);
		return pos;
	}

	@Override
	public List<Postulantes> obtenerPostulantesPorUbicacion(int codDepa) {
		
		List<Postulantes> pos = new ArrayList<Postulantes>();
		String sql = "Select * From  postulantes where depar  = ? and and estado=1 )";
		Postulantes post = new Postulantes();

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = dataSource.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, codDepa);
			rs = ps.executeQuery();

			while (rs.next()) {
				post = new Postulantes();
				post.setDni(rs.getInt("dni"));
			    post.setCorreo(rs.getString("correo"));
				pos.add(post);
			}

		} catch (SQLException ex) {

		}
		closeConecction(rs, ps, conn);
		return pos;
	}
	

}
